#-*-coding:utf-8-*-
# Create your views here.
from crm.models import Role
import role_cfg as rcfg
from crm.models import Agent
from crm.models import Furtherinterest
from crm.models import Projects,Project_course_info
from crmv2.models import Student,Program_student_course,Student_Payment_Record
from models import Follow_up,Next_Follow_Date
from crm.models import Sales_record
#from crm.models import Maillist,Maillog
from django.shortcuts import get_object_or_404,render_to_response
from django.http import Http404, HttpResponse, HttpResponseRedirect
#from django import forms
from django.core.paginator import Paginator, InvalidPage
import time,datetime,check
from django.db import connection 
import logClass as log

log = log.Log()

def afterWeekDate():
    today = datetime.datetime.today()
    aWeekDelta = datetime.timedelta(weeks=1)
    aWeekAgo = today - aWeekDelta
    return datetime.datetime.strftime(aWeekAgo,'%Y-%m-%d')

def getTwoDateTolMonth(begin,end):
    begy,begm = time.strptime(begin,'%Y-%m-%d')[0:2]
    endy,endm = time.strptime(end,'%Y-%m-%d')[0:2]
    ret = []
    temp = {'year':'','length':0,'value':[]}

    if `begy`==`endy`:
        temp['year'] = `begy`
        temp['value'] = range(begm,endm+1) 
        temp['length'] = len(temp['value'])
        ret.append(temp)
    else:
        difyear = endy-begy
        i = 1
        temp['year'] = `begy`
        temp['value'] = range(begm,13)
        temp['length'] = len(temp['value'])
        ret.append(temp)
        temp = {'year':'','value':[]}
        while i<difyear:
            temp['year']=`begy+i`
            temp['value']=range(1,13)
            temp['length'] = len(temp['value'])
            ret.append(temp)
            temp = {'year':'','value':[]}
            i+=1

        temp['year'] = `endy`
        temp['value'] = range(1,endm+1)
        temp['length'] = len(temp['value'])
        ret.append(temp)
        
    return ret


"""查看roles列表清单"""
def attend_list (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',request.session['program']))
    period = request.GET.get('period',0)

    error_info = ''

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    periods = ''
    s_list = []
    module = ''
    professor = ''
    venue = ''
    date1 = ''
    date2 = ''
    date3 = ''
    date4 = ''
    sql1 = ''
    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            where += ''' and a.class_start>="%s" and a.class_start<="%s"'''%(begin,end)
            if p_id:
                where += ''' and a.`project_id`=%s'''%p_id
        
            sql = '''select a.`period` from `project_course_info` a %s group by a.`period` order by a.`class_start`'''%where
            cursor.execute(sql) 
            periods = cursor.fetchall()

            if not period and periods:
                period = periods[0]
            
            if period:
                where += ''' and a.`period`="%s"'''%period
                sql1 = '''select a.`modules`,a.`lecture`,a.`venue`,b.`fullname`,a.`class_start`,a.`class_end`,a.`class_date3`,a.`class_date4` from `program_student_course` a join `crmv2_student` b on b.`id`=a.`student_id` %s group by b.`fullname`'''%where
                cursor.execute(sql1) 
                students = cursor.fetchall()
                for s in students:
                    s_list.append(s[3])
                module = s[0]
                professor  = s[1]
                venue = s[2]
                date1 = s[4]
                date2 = s[5]
                date3 = s[6]
                date4 = s[7]
        except Exception,e:
            log.error(e)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/management/attend_list.html',{
        'begin':begtime,
        'end':endtime,
        'date1':date1,
        'date2':date2,
        'date3':date3,
        'date4':date4,
        'p_id':request.session['program'],
        'module':module,
        'professor':professor,
        'venue':venue,
        'periods':periods,
        'period':period,
        'error_info':error_info,
        'project_list':project_list,
        'is_con':is_con,
        's_list':s_list,
        'ttt':''
    })

def attend_report (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',0))

    error_info = ''

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    
    tol_ret = []

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        #try:
        projects = {}
        pro_temp = []
        for project in project_list:
            projects[project.id]=project.name.replace(' ','').replace('&','')
            pro_temp.append(project.name.replace(' ','').replace('&',''))

        cursor = connection.cursor()
        where += ''' and class_start>="%s" and class_start<="%s"'''%(begin,end)
        if p_id:
            where += ''' and `project_id`=%s'''%p_id
    
        sql = '''SELECT YEAR(`class_start`),MONTH(  `class_start` ) ,  `project_id` , COUNT( * ) 
                    FROM  `program_student_course` %s
                    GROUP BY YEAR(`class_start`),MONTH(  `class_start` ) ,  `project_id` order by YEAR(`class_start`),MONTH(  `class_start` )'''%where
        cursor.execute(sql) 
        sql1 = cursor.fetchall()
        value = []
        for i in pro_temp:
            value.append(0)

        temp_y = {'year':'','month':[]}
        temp_m = {'name':'','value':value[:]}
        flag = 1
        for ret in sql1:
            if not projects.has_key(ret[2]):
                continue;
            if ret[0]==temp_y['year']:
                if temp_m['name']==ret[1]:
                    temp_m['value'][pro_temp.index(projects[ret[2]])]=ret[3]
                else:
                    temp_y['month'].append(temp_m)
                    temp_m = {'name':'','value':value[:]}
                    temp_m['name'] = ret[1]
                    temp_m['value'][pro_temp.index(projects[ret[2]])]=ret[3]
            else:
                if flag==0:
                    temp_y['month'].append(temp_m)
                    tol_ret.append(temp_y)
                    temp_y = {'year':'','month':[]}
                    temp_m = {'name':'','value':value[:]}
                temp_y['year'] = ret[0]
                temp_m['name'] = ret[1]
                temp_m['value'][pro_temp.index(projects[ret[2]])]=ret[3]
                flag = 0
        temp_y['month'].append(temp_m)
        tol_ret.append(temp_y)
        if tol_ret == [{'month': [{'name': '', 'value': value[:]}], 'year': ''}]:
            tol_ret = []
        del(temp_y)
        del(temp_m)

        #except Exception,e:
            #log.error(e)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/management/attend_report.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'is_con':is_con,
        'error_info':error_info,
        'tol_ret':tol_ret,
        'ttt':''
    })

def class_list (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',request.session['program']))
    period = request.GET.get('period',0)

    error_info = ''

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    periods = ''
    s_list = []
    module = ''
    professor = ''
    venue = ''
    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            where += ''' and a.class_start>="%s" and a.class_start<="%s"'''%(begin,end)
            if p_id:
                where += ''' and a.`project_id`=%s'''%p_id
        
            sql = '''select a.`period` from `project_course_info` a %s group by a.`period` order by a.`class_start`'''%where
            cursor.execute(sql) 
            periods = cursor.fetchall()

            if not period and periods:
                period = periods[0]
            
            if period:
                where += ''' and a.`period`="%s"'''%period
                sql1 = '''select a.`modules`,a.`lecture`,a.`venue`,b.`fullname`,b.`email`,b.`email2`,b.`address`,b.`tel_h`,b.`tel_o`,b.`tel_m`,b.`remark` from `program_student_course` a join `crmv2_student` b on b.`id`=a.`student_id` %s group by b.`fullname`'''%where
                cursor.execute(sql1) 
                students = cursor.fetchall()
                for s in students:
                    s_list.append((s[3],s[4],s[5],s[6],s[7],s[8],s[9],s[10]))
                module = s[0]
                professor  = s[1]
                venue = s[2]
        except Exception,e:
            log.error(e)

    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/management/class_list.html',{
        'begin':begtime,
        'end':endtime,
        'p_id':request.session['program'],
        'module':module,
        'professor':professor,
        'venue':venue,
        'periods':periods,
        'period':period,
        'error_info':error_info,
        'project_list':project_list,
        'is_con':is_con,
        's_list':s_list,
        'ttt':''
    })

def class_size (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',request.session['program']))

    error_info = ''
    static_ret = []
    sta_y = [0,0,0,0,0,0,0,0,0,0,0,0]

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'

    if not begin:
        begin = datetime.datetime.strftime(datetime.datetime.today(),'%Y-01-01')

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    tolmonth = getTwoDateTolMonth(begin,end)
    year_len = {} #{'2012':'5'}记录2012年有个有月份被统计
    year_mon = {} #{'2012':[12,11,10,9,8]} 2012年被统计的5个具体月份
    stat_list = []
    temp_value = []
    for t in tolmonth:
        year_len[t['year']] = t['length']
        year_mon[t['year']] = t['value']
        for tv in t['value']:
            stat_list.append(t['year']+`tv`)
            temp_value.append(0)
    
    if error_info:
        pass;
    else:
        #try:
        cursor = connection.cursor()
        where += ''' and class_start>="%s" and class_start <="%s"'''%(begin,end)

        if p_id:
            where += ''' and `project_id`=%s'''%p_id
        
        module_sql = '''select modules,`core_elective`,class_start,class_end,class_date3,class_date4,class_date5 from `project_course_info` %s and modules<>'' group by modules order by class_start desc'''%where
        cursor.execute(module_sql) 
        modules_list = cursor.fetchall()
        
        sta_sql = '''select `modules`,YEAR(class_start),MONTH(class_start),count(*) from `program_student_course` %s group by `modules`,YEAR(class_start),MONTH(class_start) order by YEAR(class_start),MONTH(class_start)'''%where
        cursor.execute(sta_sql) 
        sta_list = cursor.fetchall()
        temp = {'name':'','ce':'','date1':'','date2':'','date3':'','date4':'','date5':'','value':temp_value[:]}
        i = 0
        for mod in modules_list:
            i+=1
            temp['name']=mod[0]
            temp['ce']=mod[1]
            temp['date1']=mod[2]
            temp['date2']=mod[3]
            temp['date3']=mod[4]
            temp['date4']=mod[5]
            temp['date5']=mod[6]
            flag = 1
            
            for sta in sta_list:
                if sta[0]==temp['name']:
                    temp['value'][stat_list.index(`int(sta[1])`+`int(sta[2])`)] = sta[3]
                else:
                    continue
            static_ret.append(temp)
            temp = {'name':'','ce':'','date1':'','date2':'','date3':'','date4':'','date5':'','value':temp_value[:]}

        #except Exception,e:
            #log.error(e)
    
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/management/class_size.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'is_con':is_con,
        'error_info':error_info,
        'tolmonth':tolmonth,
        'static_ret':static_ret,
        'ttt':''
    })

def group (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',0))

    error_info = ''

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            where += ''' and regtime>="%s" and regtime <="%s"'''%(begin,end)
            if p_id:
                where += ''' and `projects_id`=%s'''%p_id
        
            sql1 = '''SELECT `agent_id`,`projects_id`,count(`projects_id`) FROM `crm_members` %s group by `agent_id`,`projects_id` order by `agent_id` desc,`projects_id` desc'''%where
            cursor.execute(sql1) 
            sql = cursor.fetchall()
            
            
        except Exception,e:
            log.error(e)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/management/group.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'error_info':error_info,
        'is_con':is_con
    })

def individual (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    stu_name_id = request.GET.get('stu_name_id',0)
    p_id = int(request.GET.get('p_id',request.session['program']))

    error_info = ''
    names = ''
    sql1 = ''
    mod = ''
    stu = ''
    module_sql = ''
    module_ret = []
    pay_list = []
    tolmoney = ''

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'

    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            name_sql = '''select b.`id`,b.`fullname`,b.`name_en`,b.`tel_h`,b.`tel_o`,b.`tel_m`,b.`email`,b.`email2`,b.`start_date`,b.`address`,b.`officeaddress` from program_student_course a join crmv2_student b on b.id=a.student_id where a.project_id=%s group by b.fullname,b.`name_en`'''%(p_id)
            cursor.execute(name_sql) 
            name_list = cursor.fetchall()
            names = []
            temp = []
            for name in name_list:
                temp = list(name)
                temp[9]=temp[9].replace('\r\n'," ")
                temp[10]=temp[10].replace('\r\n'," ")
                names.append(temp)
            
            if not stu_name_id and names:
                stu_name_id = names[0][0]
            
            if stu_name_id:

                if p_id:
                    where += ''' and a.`project_id`=%s'''%p_id
                
                module_sql = '''select a.modules,a.`core_elective`,a.class_start,a.class_end,a.class_date3,a.class_date4,a.class_date5 from `project_course_info` a %s and a.modules<>'' group by a.modules order by a.class_start desc'''%where
                cursor.execute(module_sql) 
                modules_list = cursor.fetchall()
                
                where += ''' and a.`student_id`=%s'''%stu_name_id

                sql1 = '''select a.modules,a.status from `program_student_course` a %s group by a.modules order by a.modules desc,a.status'''%where
                cursor.execute(sql1)
                stu_lists = cursor.fetchall()
                
                temp = []
                for mod in modules_list:
                    temp = list(mod)
                    for stu in stu_lists:
                        if `stu[0]` == `mod[0]`:
                            temp.append(stu[1])
                        if stu[1][1:]=='ompleted':
                            break
                    module_ret.append(temp)
                
                try:
                    pp_obj = get_object_or_404(Projects, pk = int(p_id))
                    s_obj = get_object_or_404(Student, pk=stu_name_id)
                except Exception,e:
                    log.error(`e`)

                pay_list = Student_Payment_Record.objects.filter(project=pp_obj,student=s_obj)
                if pay_list:
                    tolmoney = {}
                    pay_list = pay_list[0]

                    if tolmoney.has_key(pay_list.moneytypedatefee) and pay_list.datefee_money:
                        tolmoney[pay_list.moneytypedatefee] += int(pay_list.datefee_money)
                    elif pay_list.datefee_money:
                        tolmoney[pay_list.moneytypedatefee] = int(pay_list.datefee_money)
                    
                    if tolmoney.has_key(pay_list.moneytypedate1) and pay_list.date1_money:
                        tolmoney[pay_list.moneytypedate1] += int(pay_list.date1_money)
                    elif pay_list.date1_money:
                        tolmoney[pay_list.moneytypedate1] = int(pay_list.date1_money)
                    
                    if tolmoney.has_key(pay_list.moneytypedate2) and pay_list.date2_money:
                        tolmoney[pay_list.moneytypedate2] += int(pay_list.date2_money)
                    elif pay_list.date2_money:
                        tolmoney[pay_list.moneytypedate2] = int(pay_list.date2_money)

                    if tolmoney.has_key(pay_list.moneytypedate3) and pay_list.date3_money:
                        tolmoney[pay_list.moneytypedate3] += int(pay_list.date3_money)
                    elif pay_list.date3_money:
                        tolmoney[pay_list.moneytypedate3] = int(pay_list.date3_money)
                    
                    if tolmoney.has_key(pay_list.moneytypedate4) and pay_list.date4_money:
                        tolmoney[pay_list.moneytypedate4] += int(pay_list.date4_money)
                    elif pay_list.date4_money:
                        tolmoney[pay_list.moneytypedate4] = int(pay_list.date3_money)
                    tolmoney = `tolmoney`.replace('u','')[1:-1]
        except Exception,e:
            log.error(e)
    
    return render_to_response('report/management/individual.html',{
        'project_list':project_list,
        'is_con':is_con,
        'stu_name_id':int(stu_name_id),
        'stu_names':names,
        'error_info':error_info,
        'module_ret':module_ret,
        'pay_list':pay_list,
        'tolmoney':tolmoney,
        'ttt':''
    })

def student (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',request.session['program']))

    error_info = ''
    stu_ret = ''

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            where += ''' and a.class_start>="%s" and a.class_start <="%s"'''%(begin,end)
            if p_id:
                where += ''' and a.`project_id`=%s'''%p_id
        
            sql1 = '''select b.last_name,b.fullname,b.name_en,b.name_ch,b.tel_h,b.tel_o,b.tel_m,b.email,b.email2,b.university1,b.university2,b.awarding1,b.employer,b.title from `program_student_course` a join `crmv2_student` b on b.id=a.student_id %s group by a.student_id order by class_start desc'''%where
            cursor.execute(sql1) 
            stu_ret = cursor.fetchall()
            
        except Exception,e:
            log.error(e)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/management/student.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'error_info':error_info,
        'is_con':is_con,
        'stu_ret':stu_ret,
        'ttt':''
    })

def tentative (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',0))

    error_info = ''
    ten_ret = []

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            where += ''' and class_start>="%s" and class_start<="%s"'''%(begin,end)
            if p_id:
                where += ''' and `project_id`=%s'''%p_id
        
            sql1 = '''select `core_elective`,`coursecode`,`class_start`,`class_end`,`class_date3`,`class_date4`,`class_date5`,`lecture`,`modules`,`season`,`year` from `project_course_info` %s group by `modules` order by `class_start` desc'''%where
            cursor.execute(sql1) 
            ten_ret = cursor.fetchall()
            
            
        except Exception,e:
            log.error(e)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/management/tentative.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'error_info':error_info,
        'is_con':is_con,
        'ten_ret':ten_ret,
        'ttt':''
    })
